# -*- coding: utf-8 -*-
# paho mqtt 中文文档 https://blog.csdn.net/weixin_41656968/article/details/80848542 官网文档 https://pypi.org/project/paho-mqtt/
# pymysql 中文文档 https://www.cnblogs.com/Equilibrium/p/12170365.html
# mysql 用户管理 https://www.cnblogs.com/keme/p/10288168.html

import json
import random
import time

import paho.mqtt.client as mqtt
import pymysql

# 时间控制
topic_update_table = {
    "ZUCC-ZYY/esp": {
        "last_time": 1609781077,
        "space_time": 600  # 十分钟更新一次
    },
    "topic_name": {
        # UTC时间 单位_秒
        "last_time": 1609781077,  # 这个数字，就是大概 2021年1月5日 01:24:30
        # 间隔时间 单位_秒
        "space_time": 1
    },
}


def is_should_update(topic):
    """
    判断 topic 的信息据上一次的间隔，返回是否可以持久化到 mysql

    - Args:
        - topic `String`: 待判断的Topic

    - Returns:
        - `Boolean`: 返回是否可以持久化到 mysql
    """
    opt = topic_update_table.get(topic, False)
    if opt:
        time_now = int(time.time_ns()/1e9)
        # print("检查 ", topic, time_now, time_now-opt['last_time'] >= opt['space_time'])
        if time_now-opt['last_time'] >= opt['space_time']:
            topic_update_table[topic]["last_time"] = time_now
            return True
        else:
            print("%s 更新间隔设定为 %d 秒，现在据上次更新相差 %d 秒，" %
                  (topic, opt['last_time'], time_now-opt['last_time']))
            return False
    else:
        return True


def execute_mysql(sql):
    """
    执行 sql 语句

    - Args:
        - sql `String`: 待执行的语句

    - Returns:
        - `ResultSet`: 返回 sql 语句的执行结果
    """
    conn = pymysql.connect(host='www.zdiml.xyz',
                           port=3306,
                           user='IoTZYY',
                           password='20200104',
                           database='IoTZYY')
    cur = conn.cursor()
    print("\n--------------------connect----------------------")
    try:
        # 执行语句
        print(sql)
        cur.execute(sql)
        # 保存执行结果
        rs = cur.fetchall()
        print("--------------------success---------------------")
    except Exception as e:
        return e
    finally:
        # 提交事务
        conn.commit()
        # 关闭游标 关闭连接
        cur.close()
        conn.close()
    return rs


def is_json(myjson):
    """
    判断字符串是否是JSON

    - Args:
        - myjson `String`: 待判断的字符串

    - Returns:
        - `Boolean`: 判断的结果
    """
    try:
        json.loads(myjson)
    except ValueError:
        return False
    return True


def json_sql(table, key_s, msg_dict):
    """
    生成指定的 Insert 语句
    - Args:
        - table `String`: 要插入的表名
        - key_s `String[]`: 要插入的列名的列表
        - msg_dict `dict`: 插入列名的对应值的字典
    - Returns:
        - String: 生成的SQL语句
    """

    val_s = []
    sql = "insert into `%s`(" % table
    for key in key_s[0:-1]:
        sql += '`%s`,' % key
        val_s.append(msg_dict[key])
    sql += '`%s`)values(' % key_s[-1]
    for val in val_s:
        sql += '\'%s\',' % val
    sql += '\'%s\')' % msg_dict[key_s[-1]]
    return sql


# 收到消息以后 回调函数
def on_message(client, userdata, msg):
    payload_str = str(msg.payload.decode('utf-8'))
    payload_json = {}
    if is_json(payload_str):
        payload_json = json.loads(payload_str)
    if "close" != payload_str:
        if payload_json == {}:
            print(payload_str)
        else:
            if msg.topic == "ZUCC-ZYY/esp":
                if is_should_update(msg.topic):
                    data = {}
                    data['hum'] = payload_json.get('humidity')
                    data['tem'] = payload_json.get('temperature')
                    data['time'] = time.strftime(
                        '%Y-%m-%d %H:%M:%S', time.localtime())
                    # ResultSet
                    rs = execute_mysql(
                        json_sql('DHT11', ['tem', 'hum', 'time'], data))
                    print(rs)
                else:
                    pass
                    # print(msg.topic, "未到更新时间")


def on_disconnect(client, userdata, rc):
    if rc != 0:
        print("Unexpected disconnection %s" % rc)


if __name__ == "__main__":
    # -------------------------------------------------------- #
    HOST = "47.93.197.229"
    PORT = 8083  # websockets PORT
    # PORT = 1883 # tcp PORT
    client_id = "zyy_python"
    topics = ["ZUCC-ZYY/#"]
    # -------------------------------------------------------- #
    client = mqtt.Client(
        client_id+"".join(random.sample('zyxwvutsrqponmlkjihgfedcba0123456789', 7)), transport="websockets")
    # client = mqtt.Client(client_id, transport="tcp")
    # -------------------------------------------------------- #
    client.on_message = on_message
    client.on_disconnect = on_disconnect
    client.connect(HOST, PORT, 60)
    # -------------------------------------------------------- #
    for item in topics:
        client.subscribe(item, 1)
    # -------------------------------------------------------- #
    print("监听开始")
    client.loop_forever()
    # -------------------------------------------------------- #


def store():
    json_sql(
        'DHT11', [
            'stu_order', 'stu_name', 'tty_name', 'temperature', 'humidity',
            'username'
        ], {
            'stu_order': 31801146,
            'stu_name': '章一砚',
            'tty_name': '百元ESP8266_1',
            'temperature': 12.3,
            'humidity': 0.70,
            'username': 'IOT Lab 9'
        })
    "insert into `DHT11`(`stu_order`,`stu_name`,`tty_name`,`temperature`,`humidity`,`username`)values('31801146','章一砚','百元ESP8266_1','12.3','0.7','IOT Lab 9')"
    # 2020 - 01 - 04 物联网大作业 dht持久化 到 www.zdiml.xyz IoTZYY.DHT11 20200104
    json_sql('DHT11', ['tem', 'hum', 'time'], {
        'tem': 24.8,
        'hum': 61.7,
        'time': time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
    })
    "insert into `DHT11`(`tem`,`hum`,`time`)values('24.8','61.7','2021-01-04 23:49:40')"
